<?php

namespace App\Admin\Http\Controllers\Business;

use DB;
use Log;
use Flash;
use App\Eloquent\User;
use App\Eloquent\UserInfo;
use Illuminate\Http\Request;
use App\Admin\Eloquent\OrganizationalUser;
use App\Admin\Http\Controllers\Controller;

/**
 * 销售统计控制器
 */
class SalesStatisticsController extends Controller
{
	/**
	 * 访问级别
	 *  
	 *  0	管理员
	 *  1	销售总监
	 *  2	团队经理
	 *  3	瑞信营销,做特殊处理
	 */
	protected $access_level;

	protected $RXYX_CODE = '9DS12W';	//瑞信营销 机构code

	/**
	 * 访问的前台用户(如果存在)
	 * @var [type]
	 */
	protected $frontUser;

	/**
	 * 查询的机构
	 */
	protected $org_code;

	/**
	 * 查询的部门
	 */
	protected $dept_code;

	/**
	 * 后台用户
	 */
	protected $user;

	/**
	 * 初始化
	 * 
	 * @return [type] [description]
	 */
	public function __init(){
		$this->accessInfo();

		//共享变量到视图
		view()->share('admin_user',$this->user);
		view()->share('rxyx_code',$this->RXYX_CODE);
	}

	/**
	 * 获取访问信息
	 * 
	 * @return [type] [description]
	 */
	protected function accessInfo(){
		$user = $this->currentUser();

		$this->user = $user;
		//拆分用户名,获取用户前台用户
		$name = substr($user->username, 0, 4);

		if($name == 'bbl_'){
			$name = substr($user->username, 4);
			//查找前台用户
			if($frontUser = User::toUser($name)){
				$this->frontUser = $frontUser;
				$this->org_code = $frontUser->userInfo->org_code;
				$this->dept_code = $frontUser->userInfo->dept_code;
				//判断用户角色
			}
		}

		//判断用户的角色
		if($user->hasRole('CSO')){
			$this->access_level = 1;
		}else if($user->hasRole('Team_manager')){
			$this->access_level = 2;
		}
		else if($user->hasRole('RXYX'))
		{
			$this->access_level = 3;
		}
		else
		{
			$this->access_level = 0;
		}
	}

	/**
	 * 根据对应的访问权限处理
	 * 
	 * @param  array  $queryArgs [description]
	 * @return [type]            [description]
	 */
	protected function queryRestriction(array $queryArgs){
		if(!empty($this->org_code) && ($this->access_level == 1 or $this->access_level == 2))
			$queryArgs['org_code'] = $this->org_code;

		if(!empty($this->dept_code) && $this->access_level == 2)
			$queryArgs['dept_code'] = $this->dept_code;

		return $queryArgs;
	}

	/**
	 * 营销机构销售情况统计
	 * 
	 * @return [type] [description]
	 */
	public function index(Request $request){

		//获取查询条件
		$query = $request->only('username', 'broker_name', 'start_time', 'end_time', 'dept_code', 'org_code');
		$query = $this->queryRestriction($query);

		//查询所有机构
		$all_org = OrganizationalUser::all();

		//查询销售统计
		$invest_statistics = $this->user_invest_statistics($query);

		//计算 饼图 显示位置
		$i = 0;

		foreach ($invest_statistics as $key => $value) {
			if($i<=3){
				if(count($invest_statistics) <= 4){
					$invest_statistics[$key]['x'] = (($i+1)*20).'%';
					$invest_statistics[$key]['y'] = '50%';
				}else{
					$invest_statistics[$key]['x'] = (($i+1)*20).'%';
					$invest_statistics[$key]['y'] = '25%';
				}
			}else{
				$invest_statistics[$key]['x'] = (($i-3)*20).'%';
				$invest_statistics[$key]['y'] = '75%';
			}
			$i++;
		}

		unset($i);

		//查询用户投资列表
		$user_invest_list = $this->user_invest_list($query);

		$links = $user_invest_list->render();

		return view('admin.business.sales_statistics.index',compact('invest_statistics', 'user_invest_list', 'org_user', 'query', 'links', 'all_org'));
	}


	/**
	 * 查询指定机构的用户投资金额统计数据
	 * 
	 * @param  string $org_code 机构简码
	 * @param  array  $args     查询参数
	 * @return [type]           统计数据数组
	 */
	public function user_invest_statistics(array $args,int $paginate_size = 15){

		//统计用户投资情况
		$query_one = DB::table('projects_user as a')
			->leftJoin('projects as b', 'a.projects_id', '=', 'b.id')
			->leftJoin('user_info as c', 'a.user_id', '=', 'c.user_id')
			->leftJoin('codes as d', 'b.status', '=', 'd.code')
			->leftJoin('user_info as e', 'c.broker_code', '=', 'e.invite_code')
			->select(DB::raw('count(distinct a.user_id) as person_number,
       d.name,b.status as invest_status,
       sum(a.invest_money) as invest_money,count(a.id) as transaction_pen'));


		//统计用户总投资数和总投资笔数，总投资人数
		$query_two = DB::table('projects_user as a')
			->leftJoin('projects as b', 'a.projects_id', '=', 'b.id')
			->leftJoin('user_info as c', 'a.user_id', '=', 'c.user_id')
			->leftJoin('user_info as e', 'c.broker_code', '=', 'e.invite_code')
			->select(DB::raw('sum(a.invest_money) as invest_money,count(distinct a.user_id) as person_number,count(a.id) as transaction_pen'));


		//追加查询条件
		if(!empty($args['org_code'])){
			$query_one = $query_one->where('c.org_code', $args['org_code']);
			$query_two = $query_two->where('c.org_code', $args['org_code']);
		}

		if(!empty($args['username'])){
			$query_one = $query_one->where('c.name', 'like', '%'.$args['username'].'%');
			$query_two = $query_two->where('c.name', 'like', '%'.$args['username'].'%');
			// dd($args);
		}


		if(!empty($args['broker_name'])){
			$query_one = $query_one->where('e.name', 'like', '%'.$args['broker_name'].'%');
			$query_two = $query_two->where('e.name', 'like', '%'.$args['broker_name'].'%');
		}

		if(!empty($args['start_time'])){
			$query_one = $query_one->where('a.invest_time', '>=', $args['start_time']);
			$query_two = $query_two->where('a.invest_time', '>=', $args['start_time']);
		}

		if(!empty($args['end_time'])){
			$query_one = $query_one->where('a.invest_time', '<=', $args['end_time']);
			$query_two = $query_two->where('a.invest_time', '<=', $args['end_time']);
		}

		if(!empty($args['dept_code'])){
			$query_one = $query_one->where('c.dept_code', '=', $args['dept_code']);
			$query_two = $query_two->where('c.dept_code', '=', $args['dept_code']);
		}


		$query_one = $query_one->groupBy('b.status')->get();
		$query_two = $query_two->first();


		//数据组装
		$user_invest_statistics = [
			'all' => [
				'money' => $query_two->invest_money??0,
				'person_number' => $query_two->person_number,
				'transaction_pen' => $query_two->transaction_pen,
				'name' =>'总投资'
			],
			'stock' =>[
				'money' => 0,
				'person_number' => 0,
				'transaction_pen' => 0,
				'name' =>'存量'
			]
		];


		// dd($user_invest_statistics);
		// dd($user_invest_statistics);
		
		foreach ($query_one as $data) {
			$user_invest_statistics[$data->invest_status]=[
				'money' => $data->invest_money,
				'person_number' => $data->person_number,
				'transaction_pen' => $data->transaction_pen,
				'name'=> $data->name
			];
		};

		//存量计算
		foreach ($query_one as $v) {
			switch ($v->invest_status) {
				case 'payment':
				case 'rush':
				case 'full':
				case 'late':
					$user_invest_statistics['stock']['money']+=$v->invest_money;
					$user_invest_statistics['stock']['transaction_pen']+=$v->transaction_pen;
					$user_invest_statistics['stock']['person_number']='0';
					$user_invest_statistics['stock']['name']='存量';
					break;
				default:
					# code...
					break;
			}
		}

		return $user_invest_statistics;
	}

	/**
	 * 查询用户投资记录
	 * 
	 * @param  string $org_code 机构简码
	 * @param  array  $args     查询参数
	 * @return [type]           查询结果
	 */
	public function user_invest_list(array $args,int $paginate_size = 15){
		$query = DB::table('projects_user as a')
			->leftJoin('user_info as b', 'a.broker_code', '=', 'b.invite_code')
			->leftJoin('projects as c', 'a.projects_id', '=', 'c.id')
			->leftJoin('codes as d', 'c.status', '=', 'd.code')
			->leftJoin('user_info as e', 'a.user_id', '=', 'e.user_id')
			->select(DB::raw('a.sn,a.user_id,e.name as user_name,b.name as broker_name,b.user_id as broker_id,a.invest_money,a.expected_return,c.status as invest_status,d.name as invest_status_name,c.projects_name,c.id as projects_id,a.invest_time,c.trade_repayment_date,c.trade_finish_date'));



		//追加查询条件
		if(!empty($args['username']))
			$query = $query->where('a.user_name', 'like', '%'.$args['username'].'%');


		if(!empty($args['broker_name']))
			$query = $query->where('b.name', 'like', '%'.$args['broker_name'].'%');

		if(!empty($args['start_time'])){
			$query = $query->where('a.invest_time', '>=', $args['start_time']);
		}

		if(!empty($args['end_time'])){
			$query = $query->where('a.invest_time', '<=', $args['end_time']);
		}

		if(!empty($args['org_code']))
			$query = $query->where('b.org_code', $args['org_code']);

		if(!empty($args['dept_no'])){
			$query = $query->where('e.dept_code', '=', $args['dept_no']);
		}
		

		$user_invest_list = $query->orderBy('a.invest_time', 'desc')
			->paginate($paginate_size);

		$user_invest_list = $user_invest_list->appends($args);

		return $user_invest_list;
	}

	/**
	 * 经纪人列表
	 * 
	 * @param  Request $request [description]
	 * @return [type]           [description]
	 */
	public function brokerList(Request $request){

		//获取查询条件
		$query = $this->queryRestriction($request->only('name', 'org_code', 'dept_code'));

		//查询所有机构
		$all_org = OrganizationalUser::all();

		$q = DB::table('user_info as a')
            ->leftJoin('codes as b', 'a.dept_code', '=', 'b.code')
            ->leftJoin('users as c', 'a.user_id', '=' ,'c.id')
            ->selectRaw('a.user_id,a.name,a.invite_code,b.name as dept,c.phone_number,a.created_at ')
            ->whereRaw('a.invite_code = a.broker_code');


        if(!empty($query['org_code']))
            $q = $q->where('a.org_code', $query['org_code']);

        if(!empty($query['name']))
            $q = $q->where('a.name', 'like', '%'.$query['name'].'%');

        if(!empty($query['dept_no']))
            $q = $q->where('a.dept_code', $query['dept_no']);

        $brokers = $q->orderBy('c.created_at', 'desc')->paginate(15);

        $links = $brokers->render();

		return view('admin.business.sales_statistics.brokers',compact('brokers', 'links', 'depts', 'query', 'all_org'));



	}

	/**
	 * 查询指定经纪人详情
	 * 
	 * @param  int    $id [description]
	 * @return [type]     [description]
	 */
	public function brokerInfo(int $id){
		//查询指定经纪人详情
		$user = User::findOrFail($id);

		//查询经纪人客户数
		$customers_number = $this->customers_numbers(['broker_code'=>$user->userInfo->invite_code]);

		//查询客户投资情况
		$invest_statistics = $this->user_invest_statistics($this->queryRestriction(['broker_name'=>$user->userInfo->name]));

		return view('admin.business.sales_statistics.broker_info',compact('user', 'customers_number', 'invest_statistics'));
	}

	/**
	 * 统计客户数
	 * 
	 * @param  array  $args [description]
	 * @return [type]       [description]
	 */
	public function customers_numbers(array $args){
		$query = DB::table('user_info as a')
			->whereRaw('a.invite_code <> a.broker_code');

		if(!empty($args['broker_code']))
			$query = $query->where('a.broker_code', $args['broker_code']);

		$query = $query->select(DB::raw('count(a.user_id) as count'))->first();

		return $query->count;
	}

	/**
	 * 查询投资人
	 * 
	 * @return [type] [description]
	 */
	public function userList(Request $request){

		//获取查询条件
		$query = $this->queryRestriction($request->only('userKey', 'broker_name', 'dept_code', 'org_code'));

		//查询所有机构
		$all_org = OrganizationalUser::all();

		//查询所有的投资人用户
		$users = DB::table('user_info as a')
			->join('users as b', 'a.user_id', '=', 'b.id')
			->leftJoin('user_info as c', 'a.broker_code', '=', 'c.invite_code')
			->leftJoin('codes as d', 'a.dept_code', '=', 'd.code')
			->select(DB::raw('b.id,a.name,b.username,b.phone_number,c.name as broker_name,c.user_id as broker_id,d.name as dept,a.created_at'))
			->whereRaw('a.invite_code <> a.broker_code');

		//追加查询条件
		if(!empty($query['userKey'])){
			$suers = $users->where(function($q) use ($query){
				$q = $q->where('b.username', 'like', '%'.$query['userKey'].'%')
					->orWhere('b.phone_number', 'like', '%'.$query['userKey'].'%')
					->orWhere('b.email', 'like', '%'.$query['userKey'].'%')
					->orWhere('a.name', 'like', '%'.$query['userKey'].'%');
			});

		}


		if(!empty($query['org_code']))
			$users = $users->where('a.org_code', $query['org_code']);


		if(!empty($query['dept_code']))
			$users = $users->where('a.dept_code', $query['dept_code']);

		if(!empty($query['broker_name']))
			$users = $users->where('c.name', 'like', '%'.$query['broker_name'].'%');

		$users = $users->orderBy('a.created_at', 'desc')->paginate(15);
		$links = $users->render();

		return view('admin.business.sales_statistics.users',compact('users', 'links', 'depts', 'query', 'all_org'));
	}

	/**
	 * 查询客户详情
	 * 
	 * @param  int    $id [description]
	 * @return [type]     [description]
	 */
	public function userInfo(int $id){
		//查询指定投资人详情
		$user = User::findOrFail($id);

		return view('admin.business.sales_statistics.user_info',compact('user'));
	}

	/**
	 * 查询某个用户的交易记录
	 * 
	 * @return [type] [description]
	 */
	public function transactionRecord(Request $request, int $id){
		$user = User::findOrFail($id);

        $query = $request->only('startTime', 'endTime');

        $data = DB::table('trade_records')
                    ->where(function ($q)use ($user, $query){

                        $q->where('pay_user', $user->id);
                        //追加查询条件
                        if(!empty($query['startTime'])){
                            $q = $q->where('pay_time', '>=', $query['startTime']);
                        }

                        if(!empty($query['endTime'])){
                            $q = $q->where('pay_time', '<=', $query['endTime']);
                        }

                    })
                    ->orWhere(function($q)use ($user, $query){

                        $q->where('receive_user', $user->id);

                        //追加查询条件
                        if(!empty($query['startTime'])){
                            $q = $q->where('pay_time', '>=', $query['startTime']);
                        }

                        if(!empty($query['endTime'])){
                            $q = $q->where('pay_time', '<=', $query['endTime']);
                        }

                    })
                    ->select('sn', 'remarks','pay_time','pay_user', 'receive_user', 'money', 'user_balance', 'status', 'type');

        
        //分页
        $data = $data->orderBy('pay_time','desc')->paginate(6);

        $links = $data->render();
		return view('admin.business.sales_statistics.transaction_record',compact('data','user', 'links','query'));
	}

	/**
	 * 经纪人转投资人
	 * 
	 * @return [type] [description]
	 */
	public function brokerToUser(int $user_id){
		// dd($user_id);
		//查询经纪人详情
		$broker = User::findOrFail($user_id);


		return view('admin.business.sales_statistics.broker_to_user', compact('broker', 'depts'));
	}

	/**
	 * 经纪人转投资人 处理
	 * 
	 * @return [type] [description]
	 */
	public function brokerToUserSave(Request $request){

		if(!$request->has('broker_id')){
            abort(600,'请填写归属经纪人');
        }

		if(!$request->has('gsjjrname')){
            abort(600,'请填写归属经纪人');
        }

        $broker_id = $request->input('broker_id');

        $gsjjrname = $request->input('gsjjrname');

        $jjr_user = User::findOrFail($broker_id);

        if(empty($jjr_user)||!$jjr_user->hasRole('Agent')){
            abort(600,'原经纪人用户不存在...');
        }

        $gsjjr_user = User::where('username', $gsjjrname)
            ->orWhere('email', $gsjjrname)
            ->orWhere('phone_number', $gsjjrname)
            ->first();

        if(empty($gsjjr_user)||!$gsjjr_user->hasRole('Agent')){
            abort(600,'归属经纪人用户不存在...');
        }

        $agent_id = (DB::select("select id,slug,name from roles where slug = 'Agent'"))[0]->id;
        //数据校验正确,执行变更操作
        DB::beginTransaction();
        try{
            //更改原经纪人 部门 机构 经纪人id 
            $jjr_user->userInfo->update(['broker_code' => $gsjjr_user->userInfo->invite_code, 'org_code' => $gsjjr_user->userInfo->org_code, 'dept_code' => $gsjjr_user->userInfo->dept_code]);
            //移除原经纪人(Agent)角色
            DB::table('role_user')->where('role_id', '=', $agent_id)->where('user_id', '=', $jjr_user->id)->delete();
            //更改原经纪人旗下(投资人) 部门 机构 经纪人id
            UserInfo::where('broker_code', '=', $jjr_user->userInfo->invite_code)->update(['broker_code' => $gsjjr_user->userInfo->invite_code, 'org_code' => $gsjjr_user->userInfo->org_code, 'dept_code' => $gsjjr_user->userInfo->dept_code]);

            DB::commit();
            Flash::success('经纪人转投资人成功');
            return $this->redirectAction('Business\SalesStatisticsController@brokerList');
        }catch(Exception $e){
            DB::rollback();
            throw $e;
        }

	}
	public function userToAgent(int $user_id){

		$user = User::findOrFail($user_id);

		//查询机构
		
		$all_org = OrganizationalUser::all();
		

		return view('admin.business.sales_statistics.user_to_agent', compact('user', 'all_org'));
	}	
	public function userToAgentSave(Request $request){
		// dd(1);
		$id = $request->input('user_id');
		$dept_code = $request->input('dept_code');
		$org_code = $request->input('org_code');

		if(empty($dept_code)){
			abort(600,'部门未指定');
		}

		$user = User::findOrFail($id);
		// dd(2);

        if(empty($user)||!$user->hasRole('User')||$user->hasRole(['Agent','OperatingPlatform','GuaranteeAgency','MarketingAgency'])){
            abort(600,'投资人用户不存在...');
        }

        if(!$user->isSetPayPassword()){
            abort(600,'未设置支付密码');
        }

        if($user->invite_code == $user->second_broker_code)
            abort(600,'无法将二级经纪人转换为一级经纪人');

        //执行操作
        // $org_user = User::findOrFail($org_id);
        $agent_id = (DB::select("select id,slug,name from roles where slug = 'Agent'"))[0]->id;

        DB::beginTransaction();
        try{

            $user->userInfo->update(['org_code' => $org_code, 'dept_code' => $dept_code, 'broker_code' => $user->userInfo->invite_code]);
            DB::insert('insert into role_user value('.$user->id.','.$agent_id.')');

            DB::commit();
            Flash::success('投资人转经纪人成功');
            return $this->redirectAction('Business\SalesStatisticsController@userList');        
        }catch(Exception $e){
            DB::rollback();
            throw $e;
        }
	}
	/**
	 * [salesStatistics description]
	 * @return [type] [description]
	 */
	public function salesStatistics(Request $request, string $download = ''){

		$query = $this->queryRestriction($request->only('start_time', 'end_time', 'username', 'org_code', 'dept_code', 'cl_time', 'broker_name'));

		if(empty($query['start_time']))
			$query['start_time'] = date('Y-m-d 00:00:00');

		if(empty($query['end_time']))
			$query['end_time'] = date('Y-m-d 23:59:59');

		if(empty($query['cl_time']))
			$query['cl_time'] = date('Y-m-d H:i:s');
		//查询所有的机构
		$all_org = OrganizationalUser::all();

		//
		

		$data = UserInfo::leftJoin(DB::raw("(select b.user_id,sum(b.invest_money) as tz from projects_user as b where b.invest_time >= '".$query['start_time']."' and b.invest_time <= '".$query['end_time']."' group by b.user_id) as d"), 'user_info.user_id', '=', "d.user_id")
					//还款
					->leftJoin(DB::raw("(select pp.user_id,sum(pp.money) as hk from project_profits as pp where (pp.type = 1 and pp.pay_time is null and pp.input_time >= '".$query['start_time']."' and pp.input_time <= '".$query['end_time']."') or (pp.type = 1 and pp.pay_time is not null and  pp.pay_time >= '".$query['start_time']."' and pp.pay_time <= '".$query['end_time']."') group by pp.user_id) as e"), 'user_info.user_id', '=', "e.user_id")

					->leftJoin(DB::raw("(select pp.user_id,sum(pp.money) as sdcl from project_profits as pp left join projects as ps on pp.project_id = ps.id where pp.type = 1 and ps.interest_at is not null and ps.interest_at <= '".$query['cl_time']."' and (pp.pay_time is null or pp.pay_time >= '".$query['cl_time']."') group by pp.user_id) as k"), 'user_info.user_id', '=', 'k.user_id')
					//当前未封包
					->leftJoin(DB::raw("(select h.user_id,sum(h.invest_money) as sdcl from projects_user as h left join projects as i on h.projects_id = i.id where i.status = 'rush' or i.status = 'full' group by h.user_id) as j"), 'user_info.user_id', '=', 'j.user_id')
					//当前为还款
					->leftJoin(DB::raw("(select aa.user_id,sum(aa.invest_money) as sdcl from projects_user as aa left join projects as ii on aa.projects_id = ii.id where ii.status = 'rush' or ii.status = 'payment' or ii.status = 'late' or ii.status = 'full' group by aa.user_id) as jj"), 'user_info.user_id', '=', 'jj.user_id')
					//用户基本信息
					->leftJoin('users as f', 'user_info.user_id', '=', 'f.id')
					//用户投资笔数
					->leftJoin(DB::raw("(select l.user_id,count(l.id) as tzbs from projects_user as l where l.invest_time >= '".$query['start_time']."' and l.invest_time <= '".$query['end_time']."' group by l.user_id) as m"), 'user_info.user_id', '=', 'm.user_id')
					//一级经纪人
					->leftJoin('user_info as jjrb', 'user_info.broker_code', '=' ,'jjrb.invite_code')
					->leftJoin('user_info as jjrb2', 'user_info.second_broker_code', '=', 'jjrb2.invite_code');
					

		if($download === 'download'){
			//是否是瑞信
			if($this->user->hasRole('RXYX'))
				$data = $data->where('user_info.org_code', $this->RXYX_CODE);

			$data = $data->select(DB::raw("user_info.user_id,user_info.name,f.username,jjrb.name as broker_name,jjrb.user_id as broker_id,jjrb2.name as two_level_broker_name,jjrb2.user_id as two_level_broker_id,f.created_at,ifnull(d.tz,0) as tz,ifnull(e.hk,0) as hk,ifnull(k.sdcl,0) as sdfbcl,ifnull(j.sdcl,0) as wfbcl,ifnull(jj.sdcl,0) as whkcl,ifnull(m.tzbs,0) as tzbs"));
		}else{
			$data = $data//查询列
					->select(DB::raw("user_info.user_id,user_info.org_code,user_info.name,f.username,jjrb.name as broker_name,jjrb.user_id as broker_id,jjrb2.name as two_level_broker_name,jjrb2.user_id as two_level_broker_id,f.phone_number,f.email,f.created_at,ifnull(d.tz,0) as tz,ifnull(e.hk,0) as hk,ifnull(k.sdcl,0) as sdfbcl,ifnull(j.sdcl,0) as wfbcl,ifnull(jj.sdcl,0) as whkcl,ifnull(m.tzbs,0) as tzbs"));
		}

		//统计合计 
		$data2 = DB::table('user_info as a')
					//投资
					->leftJoin(DB::raw("(select b.user_id,sum(b.invest_money) as tz from projects_user as b where b.invest_time >= '".$query['start_time']."' and b.invest_time <= '".$query['end_time']."' group by b.user_id) as d"), 'a.user_id', '=', "d.user_id")
					//还款
					->leftJoin(DB::raw("(select pp.user_id,sum(pp.money) as hk from project_profits as pp where (pp.type = 1 and pp.pay_time is null and pp.input_time >= '".$query['start_time']."' and pp.input_time <= '".$query['end_time']."') or (pp.type = 1 and pp.pay_time is not null and  pp.pay_time >= '".$query['start_time']."' and pp.pay_time <= '".$query['end_time']."') group by pp.user_id) as e"), 'a.user_id', '=', "e.user_id")

					->leftJoin(DB::raw("(select pp.user_id,sum(pp.money) as sdcl from project_profits as pp left join projects as ps on pp.project_id = ps.id where pp.type = 1 and ps.interest_at is not null and ps.interest_at <= '".$query['cl_time']."' and (pp.pay_time is null or pp.pay_time >= '".$query['cl_time']."') group by pp.user_id) as k"), 'a.user_id', '=', 'k.user_id')
					//当前未封包交易
					->leftJoin(DB::raw("(select h.user_id,sum(h.invest_money) as sdcl from projects_user as h left join projects as i on h.projects_id = i.id where i.status = 'rush' or i.status = 'full' group by h.user_id) as j"), 'a.user_id', '=', 'j.user_id')
					//当前未还款交易
					->leftJoin(DB::raw("(select aa.user_id,sum(aa.invest_money) as sdcl from projects_user as aa left join projects as ii on aa.projects_id = ii.id where ii.status = 'rush' or ii.status = 'payment' or ii.status = 'late' or ii.status = 'full' group by aa.user_id) as jj"), 'a.user_id', '=', 'jj.user_id')
					//用户基本信息
					->leftJoin('users as f', 'a.user_id', '=', 'f.id')
					//用户投资笔数
					->leftJoin(DB::raw("(select l.user_id,count(l.id) as tzbs from projects_user as l where l.invest_time >= '".$query['start_time']."' and l.invest_time <= '".$query['end_time']."' group by l.user_id) as m"), 'a.user_id', '=', 'm.user_id')
					->leftJoin('user_info as jjrb', 'a.broker_code', '=' ,'jjrb.invite_code');

		// $data2 = $data2->select(DB::raw('a.user_id,d.tz,e.hk,(case when ifnull(d.tz,0)>ifnull(e.hk,0) then ifnull(d.tz,0)-ifnull(e.hk,0) else 5 end) a'));

		$data2 = $data2->select(DB::raw('
			count(a.user_id) as user_count,ifnull(sum(e.hk),0) as hk_sum,count(e.hk >0) as hk_count,
			ifnull(sum(d.tz),0) tz_sum,count(tz>0) tz_count,
			ifnull(sum(j.sdcl),0) wfbcl_sum,count(j.sdcl>0) wfbcl_count,
			ifnull(sum(jj.sdcl),0) whkcl_sum,count(jj.sdcl>0) whkcl_count,
			sum(case when e.hk > d.tz then e.hk-d.tz else 0 end) ls_sum,count((e.hk-d.tz)>0) ls_count,
			ifnull(sum(case when e.hk >d.tz then d.tz else e.hk end),0) xt_sum,count((case when e.hk >d.tz then d.tz else e.hk end)>0) xt_count,
			sum(case when ifnull(d.tz,0) > ifnull(e.hk,0) then ifnull(d.tz,0)-ifnull(e.hk,0) else 0 end) xz_sum,count((case when ifnull(d.tz,0)>ifnull(e.hk,0) then ifnull(d.tz,0)-ifnull(e.hk,0) else null end)) xz_count,
			ifnull(sum(k.sdcl),0) sdcl_sum,count(k.sdcl>0) sdcl_count'));



		if(!empty('username')){
			$data = $data->where(function($q) use ($query){
				$q = $q->orWhere('f.username', 'like', '%'.$query['username'].'%')
					->orWhere('f.phone_number', 'like', '%'.$query['username'].'%')
					->orWhere('f.email', 'like', '%'.$query['username'].'%')
					->orWhere('user_info.name', 'like', '%'.$query['username'].'%');
			});
			$data2 = $data2->where(function($q) use ($query){
				$q = $q->orWhere('f.username', 'like', '%'.$query['username'].'%')
					->orWhere('f.phone_number', 'like', '%'.$query['username'].'%')
					->orWhere('f.email', 'like', '%'.$query['username'].'%')
					->orWhere('a.name', 'like', '%'.$query['username'].'%');
			});
		}

		if(!empty($query['broker_name'])){
			$data = $data->where('jjrb.name', 'like', '%'.$query['broker_name'].'%');
			$data2 = $data2->where('jjrb.name', 'like', '%'.$query['broker_name'].'%');
		}


		if(!empty($query['org_code'])){
			$data = $data->where('user_info.org_code', $query['org_code']);
			$data2 = $data2->where('a.org_code', $query['org_code']);
		}

		if(!empty($query['dept_code'])){
			$data = $data->where('user_info.dept_code', $query['dept_code']);
			$data2 = $data2->where('a.dept_code', $query['dept_code']);
		}

		$data = $data->orderBy('k.sdcl', 'desc')
			->orderBy('j.sdcl', 'desc')
			->orderBy('m.tzbs', 'desc')
			->orderBy('e.hk', 'desc')
			->orderBy('d.tz', 'desc');

		if($download === 'download'){
			$data = $data->get();
			qeury_export_excel('销售统计',$data);
		}

		$tj = $data2->orderBy('k.sdcl', 'desc')
			->orderBy('j.sdcl', 'desc')
			->orderBy('m.tzbs', 'desc')
			->orderBy('e.hk', 'desc')
			->orderBy('d.tz', 'desc')->first();

		$data = $data->paginate(15);
		$data = $data->appends($query);

		$links = $data->render();

		return view('admin.business.sales_statistics.home', compact('data', 'links', 'query', 'all_org', 'tj'));
	}
}